package com.example.delicioushunter.model.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DHDatabaseHelper extends SQLiteOpenHelper {

	private final static int DATABASE_VERSION = 1;
	private final static String DATABASE_NAME = "food_db";

	// tbl_users table
	private final static String USERS_TABLE = "tbl_users";
	private final static String CREATE_USERS = "CREATE TABLE " + USERS_TABLE
			+ "(" + UserColumns.ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ UserColumns.NAME + " varchar(20), " + UserColumns.THUMB
			+ " BLOB, " + UserColumns.AVATAR + " BLOB)";
	private final static String DROP_USERS = "DROP TABLE IF EXISTS "
			+ USERS_TABLE;

	// tbl_books table
	private final static String BOOKS_TABLE = "tbl_books";
	private final static String CREATE_BOOKS = "CREATE TABLE " + BOOKS_TABLE
			+ "(" + BookColumns.ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ BookColumns.TITLE + " VARCHAR(15), " + BookColumns.IMAGE
			+ " BLOB)";
	private final static String DROP_BOOKS = "DROP TABLE IF EXISTS "
			+ BOOKS_TABLE;

	// tbl_foods table
	private final static String FOODS_TABLE = "tbl_foods";
	private final static String CREATE_FOODS = "CREATE TABLE " + FOODS_TABLE
			+ "(" + FoodColumns.ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ FoodColumns.BOOK_ID + " INTEGER, " + FoodColumns.NAME
			+ " VARCHAR(50), " + FoodColumns.THUMB + " BLOB, "+FoodColumns.SPD_URI+" TEXT, FOREIGN KEY ("
			+ FoodColumns.BOOK_ID + ") REFERENCES " + BOOKS_TABLE + "("
			+ BookColumns.ID + "))";
	private final static String DROP_FOODS = "DROP TABLE IF EXISTS "
			+ FOODS_TABLE;

	// tbl_materials table
	private final static String MATERIALS_TABLE = "tbl_materials";
	private final static String CREATE_MATERIALS = "CREATE TABLE "
			+ MATERIALS_TABLE + "(" + MaterialColumns.ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + MaterialColumns.NAME
			+ " varchar(15), " + MaterialColumns.IMAGE + " BLOB)";
	private final static String DROP_MATERIALS = "DROP TABLE IF EXISTS "
			+ MATERIALS_TABLE;

	// tbl_materials_in_food table
	private final static String MATERIAL_IN_FOOD_TABLE = "tbl_materials_in_food";
	private final static String CREATE_MATERIAL_IN_FOOD = "CREATE TABLE "
			+ MATERIAL_IN_FOOD_TABLE + "(" + MaterialInFoodColumns.ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ MaterialInFoodColumns.MATERIAL_ID + " INTEGER, "
			+ MaterialInFoodColumns.FOOD_ID + " INTEGER, "
			+ MaterialInFoodColumns.DESCRIPTION + " VARCHAR(20), FOREIGN KEY ("
			+ MaterialInFoodColumns.MATERIAL_ID + ") REFERENCES "
			+ MATERIALS_TABLE + "(" + MaterialColumns.ID + "), FOREIGN KEY ("
			+ MaterialInFoodColumns.FOOD_ID + ") REFERENCES " + FOODS_TABLE
			+ "(" + FoodColumns.ID + "))";
	private final static String DROP_MATERIAL_IN_FOOD = "DROP TABLE IF EXISTS "
			+ MATERIAL_IN_FOOD_TABLE;

	public DHDatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		Log.d("Database", "Create new database");
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_USERS);
		Log.d("Database", "Create tbl_users");
		db.execSQL(CREATE_BOOKS);
		Log.d("Database", "Create tbl_books");
		db.execSQL(CREATE_FOODS);
		Log.d("Database", "Create tbl_foods");
		db.execSQL(CREATE_MATERIALS);
		Log.d("Database", "Create tbl_materials");
		db.execSQL(CREATE_MATERIAL_IN_FOOD);
		Log.d("Database", "Create tbl_materials_in_food");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL(DROP_MATERIAL_IN_FOOD);
		db.execSQL(DROP_MATERIALS);
		db.execSQL(DROP_FOODS);
		db.execSQL(DROP_BOOKS);
		db.execSQL(DROP_USERS);

		onCreate(db);

		Log.d("Database", "upgrade database");
	}

}
